package model;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class PrestitiImpl implements Prestiti {

	private Connection conn;

	public PrestitiImpl(Connection conn) throws SQLException, IOException {
		this.conn = conn;
	}

	public void assegnaLibroInPrestito(long isbn, int idCliente)
			throws SQLException {
		String query = "INSERT into StoricoCliente values (0,?,CURDATE(),DATE_ADD(CURDATE(),INTERVAL 30 DAY),NULL,?)";
		PreparedStatement stat = conn.prepareStatement(query);
		stat.setInt(1, idCliente);
		stat.setLong(2, isbn);
		stat.executeUpdate();
		stat.close();

	}

	public ResultSet clientiInScadenzaPrestito(int numGiorniAllaScadenza)
			throws SQLException {
		String query = "SELECT * from StoricoCliente WHERE dataStimata = DATE_ADD(CURDATE(),INTERVAL ? DAY) and dataReale is null";
		PreparedStatement stat = conn.prepareStatement(query);
		stat.setInt(1, numGiorniAllaScadenza);
		ResultSet rs = stat.executeQuery();
		return rs;
	}

	public void aggiornaPrestito(long isbn, int idCliente) throws SQLException {
		String query = "UPDATE StoricoCliente SET dataStimata = DATE_ADD(dataStimata,INTERVAL 15 DAY) WHERE StoricoCliente.Cliente_idCliente = ?  AND StoricoCliente.Libri_isbn = ? AND dataReale is null";
		PreparedStatement stat = conn.prepareStatement(query);
		stat.setInt(1, idCliente);
		stat.setLong(2, isbn);
		stat.executeUpdate();
		stat.close();
	}

	public ResultSet possoProlungarePrestito(long isbn, int idCliente)
			throws SQLException {
		String query = "SELECT DATEDIFF(CURDATE(),inizioPrestito) as numeroGiorni from StoricoCliente WHERE StoricoCliente.Libri_isbn=? AND StoricoCliente.Cliente_idCliente=? and dataReale is null";
		PreparedStatement stat = conn.prepareStatement(query);
		stat.setLong(1, isbn);
		stat.setInt(2, idCliente);
		return stat.executeQuery();
	}

	public String getDataStimata(long isbn, int idCliente) throws SQLException {
		String query = "SELECT dataStimata from StoricoCliente WHERE StoricoCliente.Libri_isbn=? AND StoricoCliente.Cliente_idCliente=? and dataReale is null";
		PreparedStatement stat = conn.prepareStatement(query);
		stat.setLong(1, isbn);
		stat.setInt(2, idCliente);
		ResultSet res = stat.executeQuery();
		res.next();
		return res.getString("dataStimata");
	}
	@Override
	public ResultSet consigliati(String genere)throws SQLException{
		
			String query = "SELECT titolo as Titolo,isbn,autore,url from StoricoCliente,Libri WHERE StoricoCliente.Libri_isbn = Libri.isbn and genere=? group by isbn ORDER BY count(*) desc Limit 3";
			PreparedStatement stat = conn.prepareStatement(query);
			stat.setString(1, genere);
			ResultSet res = stat.executeQuery();
			return res;
			
	}
	

}
